﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace SharePoint2007Migration
{
    public static class DataHelper
    {

      //  public static string ConnectionString = string.Empty;

        public static List<Dictionary<string, object>> GetData(string query, string ConnectionString)
        {
            List<Dictionary<string, object>> resultData = new List<Dictionary<string, object>>();

            SqlConnection sqlConnection = null;
            SqlCommand sqlCommand = null;
            SqlDataReader sqlDataReader = null;

            try
            {
                LogHelper.TraceDebugImportant("Enstablishing a db connection...");
                LogHelper.TraceDebug(String.Concat("Connection string: ", ConnectionString));
                LogHelper.TraceDebug(String.Concat("Query: ", query));

                sqlConnection = new SqlConnection(ConnectionString);
                sqlCommand = new SqlCommand(query, sqlConnection);
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    Dictionary<string, object> dictionary = new Dictionary<string,object>();
                    for (Int32 colIdx = 0; colIdx < sqlDataReader.FieldCount; colIdx++)
                    {
                        dictionary.Add(sqlDataReader.GetName(colIdx), sqlDataReader.GetValue(colIdx)); 
                    }
                    resultData.Add(dictionary);
                }
                LogHelper.TraceDebugImportant(String.Concat( resultData.Count, " records read"));
            }
            catch (Exception exc)
            {
                LogHelper.TraceError(exc);
                throw;
            }
            finally
            {
                if (sqlConnection != null)
                {
                    if (sqlConnection.State == System.Data.ConnectionState.Open)
                    {
                        sqlConnection.Close();
                    }
                }
            }
            return resultData;
        }

    }

}
